/**
 * 到处企业信息到Oracle数据库，以SQL的形式
 * 
 */

import { Driver } from "../db";
import * as fs from "fs";

const DB = require("../db");

let dataOpt = new Driver();



let ddl_sql: string =
    `DROP TABLE TAX_WWW_QYXX;
CREATE TABLE TAX_WWW_QYXX (
NSRZHDAH varchar2(64),
NSRMC varchar2(128),
ZCDZ varchar2(1024),
GSZCH varchar2(64),
ZZJGDM varchar2(64),
TYXYDM varchar2(64),
QYLX varchar2(64),
HY varchar2(64),
YYQX varchar2(64),
HZRQ varchar2(32),
DJJG varchar2(64),
JYFW varchar2(2048),
URL varchar2(1024),
ZYRY varchar2(2048),
GDXX varchar2(2048),
XZCF varchar2(2048)
);`;

fs.writeFileSync("./qyxx_ddl.sql", ddl_sql, { encoding: "utf-8" });

let sql_header: string = "INSERT INTO  TAX_WWW_QYXX (NSRZHDAH, NSRMC, ZCDZ, GSZCH, ZZJGDM, TYXYDM, QYLX, HY, YYQX, HZRQ, DJJG, JYFW, URL, ZYRY, GDXX, XZCF) VALUES\n";
let sql_content: string[] = [];

let getFieldValue = function (item: any, fielName: string): string {
    let value: string = item[fielName];
    if (value) return "'" + value.trim().replace(/'/g, "\'").replace(/(\r|\n)/g, "") + "'";
    return "''";
};

dataOpt.pagnate("qyxx", {}, 0, 1000).then((items: any) => {
    for (let i = 0, j = items.length; i < j; i++) {
        let item = items[i];
        let sql_line: string = "(";
        sql_line += getFieldValue(item, "纳税人账户档案号");
        sql_line += ",";
        sql_line += getFieldValue(item, "title");
        sql_line += ",";
        sql_line += getFieldValue(item, "注册地址");
        sql_line += ",";
        sql_line += getFieldValue(item, "工商注册号");
        sql_line += ",";
        sql_line += getFieldValue(item, "组织机构代码");
        sql_line += ",";
        sql_line += getFieldValue(item, "统一信用代码");
        sql_line += ",";
        sql_line += getFieldValue(item, "企业类型");
        sql_line += ",";
        sql_line += getFieldValue(item, "行业");
        sql_line += ",";
        sql_line += getFieldValue(item, "营业期限");
        sql_line += ",";
        sql_line += getFieldValue(item, "核准日期");
        sql_line += ",";
        sql_line += getFieldValue(item, "登记机关");
        sql_line += ",";
        sql_line += getFieldValue(item, "经营范围");
        sql_line += ",";
        sql_line += getFieldValue(item, "url");
        sql_line += ",";
        sql_line += getFieldValue(item, "主要人员");
        sql_line += ",";
        sql_line += getFieldValue(item, "股东信息");
        sql_line += ",";
        sql_line += getFieldValue(item, "行政处罚");
        sql_line += ")";
        sql_content.push(sql_header + sql_line);
    }

    fs.writeFileSync("./qyxx_sql.sql", sql_content.join(";\r\n") + ";", { encoding: "utf-8" });
    console.log("sql output successful!");
    process.exit(0);
}, (err: any) => {
    console.error(err)
});